ADO, set returning functions, ms acccess

--_53a13916-81b5-4271-a0cc-47baaacfec5b_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Thanks for your time...
=20
I've written a plpgsql function that produces a recordset (i think):
=20
CREATE OR REPLACE FUNCTION "public"."function_name" (int integer) RETURNS S=
ETOF "public"."tbl_nm" AS$body$/* New function body */DECLARE rec pub=
lic.tbl_nm; BEGIN FOR rec IN select * from tbl_nm wh=
ere id <> int LOOP RETURN NEXT rec; END LOOP; E=
ND;$body$LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
=20
When I execute the function from the sql editor, it produces the desired re=
sults. select * from function_name(1);
=20
When I try to incorporate this function into my ado application, it doesn't=
seem to return a recordset. =20
=20
Dim objCnn As ADODB.Connection Dim cmd As ADODB.Command Dim rs As=
ADODB.Recordset Set objCnn =3D data_layer.Open_ConnectPostGRESq=
l(objCnn) Set cmd =3D New ADODB.Command cmd.ActiveCon=
nection =3D objCnn cmd.CommandText =3D "Select * from funct=
ion_name(1);" Set rs =3D cmd.Execute
=20
rs record count is -1.
=20
Can you help?
=20
____________________________________________________________ _____
Express yourself - download free Windows Live Messenger themes!=20
http://clk.atdmt.com/MSN/go/msnnkwme0020000001msn/direct/01/ ?href=3Dhttp://=
imagine-msn.com/themes/vibe/default.aspx?locale=3Den-us&sour ce=3Dhmtagline =

--_53a13916-81b5-4271-a0cc-47baaacfec5b_
Content-Type: text/html; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

=0A=
=0A=
=0A=
=0A=
Thanks for your time...

 

I've written a plpgsql function that produces a recordset (i think):<=
/FONT>

 

CREATE OR REPLACE FUNCTION "public"."function_name" (int integer) RETURNS S=
ETOF "public"."tbl_nm" AS
$body$
/* New function body */
DECLARE R>       rec public.tbl_nm<=
/FONT>;
    BEGIN
   &nbs=
p;    FOR rec IN
      &nbs=
p;       select * from tbl_nm where id &=
lt;> int
        LOOP
 &nb=
sp;          RETURN NEXT rec;<=
BR>        END LOOP;
  &nbs=
p; END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECUR=
ITY INVOKER;

 

When I execute the function from the sql editor, it produces the desired re=
sults. select * from function_name(1);

 

When I try to incorporate this function into my ado application, it doesn't=
seem to return a recordset. 

 

    Dim objCnn As ADODB.Connection
    Dim=
cmd As ADODB.Command
    Dim rs As ADODB.Recordset
&n=
bsp;  
        Set objCnn =
=3D data_layer.Open_ConnectPostGRESql(objCnn)
    &n=
bsp;   Set cmd =3D New ADODB.Command
    &=
nbsp;  
        T>cmd.ActiveConnection =3D objCnn
    =
   
        NT>cmd.CommandText =3D "Select  * from function_name(1);=
"
       
    =
;    Set rs =3D cmd.Execute

 

rs record count is -1.

 

Can you help?

 

Re: ADO, set returning functions, ms acccess

brian stapel schrieb:
>
> When I try to incorporate this function into my ado application, it
> doesn't seem to return a recordset.
> [...]
>
> rs record count is -1.

I write without any testing or even looking at your stuff.

At least the 8.1.xx ODBC driver didn't support the recordcount of
returned recordsets.
It showed -1 regardless how many records were produced.

Try to read the recordset anyway and catch EOF to prevent a runtime-error.



---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Re: ADO, set returning functions, ms acccess

Aside from recordcount not working, your function wouldn't work from
the sql editor either because you don't have a final return statement.

Also, does Execute return a recordset or do you need to call an Open
function?

Sim
____________________________________________________________ ____________________

brian stapel schrieb:
>
> When I try to incorporate this function into my ado application, it
> doesn't seem to return a recordset.
> [...]
>
> rs record count is -1.

I write without any testing or even looking at your stuff.

At least the 8.1.xx ODBC driver didn't support the recordcount of
returned recordsets.
It showed -1 regardless how many records were produced.

Try to read the recordset anyway and catch EOF to prevent a runtime-error.



---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match